/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.di.ban.tools;

import cn.ac.iie.di.ban.commons.RuntimeEnv;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;

/**
 *
 * @author xiaoliang
 */
public class MySQLHelper {

    private static MySQLHelper mySQLHelper;
    private static Logger logger = Logger.getLogger(MySQLHelper.class.getName());
    private BasicDataSource ds;
    String driver;
    String url;
    String user;
    String pwd;
    Integer initSize;
    Integer maxSize;

    
    
    private MySQLHelper(String driver, String url, String user, String pwd, Integer initSize, Integer maxSize) {
        this.driver = driver;
        this.url = url;
        this.user = user;
        this.pwd = pwd;
        this.initSize = initSize;
        this.maxSize = maxSize;
    }

    public static synchronized MySQLHelper getMySQLHelper(String driver, String url, String user, String pwd, Integer initSize, Integer maxSize) {
        if (mySQLHelper == null) {
            mySQLHelper = new MySQLHelper(driver, url, user, pwd, initSize, maxSize);
            mySQLHelper.initDBSource();
        }
        return mySQLHelper;
    }

    private void initDBSource() {
        ds = new BasicDataSource();
        ds.setDriverClassName(driver);
        ds.setUrl(url);
        ds.setUsername(user);
        ds.setPassword(pwd);
        ds.setInitialSize(initSize);
        ds.setMaxActive(maxSize);
        ds.setValidationQuery("select 1");
        ds.setTestOnBorrow(true);
    }

    public Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    public static String getInsertSql(String table, List<String> colunms) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(table).append(" (");
        for (String col : colunms) {
            sql.append(col).append(", ");
        }
        sql.delete(sql.lastIndexOf(","), sql.lastIndexOf(",") + 2);
        sql.append(") VALUES (");
        for (String col : colunms) {
            sql.append("?,");
        }
        String insertSql = sql.substring(0, sql.length() - 1) + ")";
        return insertSql;
    }

    public static String getUpdateSql(String table, List<String> updateCols, List<String> whereCols) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(table).append(" SET ");
        for (int i = 0; i < updateCols.size(); i++) {
            sql.append(updateCols.get(i)).append("=?, ");
        }
        sql.delete(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1);
        sql.append("WHERE ");
        for (int i = 0; i < whereCols.size(); i++) {
            sql.append(whereCols.get(i)).append("=?").append(" AND ");
        }
        return sql.substring(0, sql.lastIndexOf("AND"));
    }

    public static String getDelSql(String table, List<String> whereCols) {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(table).append(" ");
        for (String col : whereCols) {
            sql.append(col).append("=? AND ");
        }
        return sql.substring(0, sql.lastIndexOf("AND"));
    }

    public static String getFindSql(String table, Set<String> returnCols, List<String> whereCols) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        if (returnCols == null || returnCols.isEmpty()) {
            sql.append("* ");
        } else {
            for (String col : returnCols) {
                sql.append(col).append(", ");
            }
            sql.delete(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1);
        }
        sql.append("FROM ").append(table).append(" WHERE ");
        for (String col : whereCols) {
            sql.append(col).append("=? AND ");

        }
        return sql.substring(0, sql.lastIndexOf("AND"));
    }

    public static String getColumns(String table) {
        return "show columns from " + table;
    }

    public static void main(String[] args) {
        List<String> list = new ArrayList<>();
        list.add("name");
        list.add("age");
        list.add("id");
        list.add("gender");
        String sql = getInsertSql("emp", list);
        System.out.println(sql);
        List<String> listw = new ArrayList<>();
        listw.add("name");
        listw.add("age");
        String sqlupdate = getUpdateSql("emp", list, listw);
        System.out.println(sqlupdate);
        System.out.println(getDelSql("table", listw));
//        System.out.println(getFindSql("table", list, listw));
        System.out.println(getColumns("table"));
    }
}
